const mysql = require('mysql2/promise');
const fs = require('fs');

async function executeAllForeignKeys() {
    let connection;

    try {
        connection = await mysql.createConnection({
            host: '43.143.253.188',
            port: 3306,
            user: 'nextjs',
            password: 'yEAiRE2jcxTyRBy5',
            database: 'nextjs',
            multipleStatements: true
        });

        console.log('✅ 数据库连接成功');
        console.log('🔗 开始创建所有外键约束...\n');

        const sqlContent = fs.readFileSync('./create-all-foreign-keys.sql', 'utf8');
        const statements = sqlContent
            .split('\n')
            .filter(line => line.trim() && !line.trim().startsWith('--'))
            .join('\n')
            .split(';')
            .filter(stmt => stmt.trim());

        console.log(`📝 准备执行 ${statements.length} 条SQL语句`);

        let successCount = 0;
        let errorCount = 0;

        for (let i = 0; i < statements.length; i++) {
            const statement = statements[i].trim();
            if (!statement) continue;

            try {
                await connection.execute(statement);
                successCount++;
                console.log(`✅ 执行成功 (${i + 1}/${statements.length}): ${statement.substring(0, 60)}...`);
            } catch (error) {
                errorCount++;
                console.log(`❌ 执行失败 (${i + 1}/${statements.length}): ${error.message}`);
                console.log(`   语句: ${statement.substring(0, 100)}...`);
            }
        }

        console.log(`\n📊 执行结果统计:`);
        console.log(`   ✅ 成功: ${successCount} 条`);
        console.log(`   ❌ 失败: ${errorCount} 条`);

        // 验证外键约束创建结果
        const [constraints] = await connection.execute(`
      SELECT 
        TABLE_NAME,
        CONSTRAINT_NAME,
        COLUMN_NAME,
        REFERENCED_TABLE_NAME,
        REFERENCED_COLUMN_NAME
      FROM information_schema.KEY_COLUMN_USAGE 
      WHERE TABLE_SCHEMA = 'nextjs' 
        AND REFERENCED_TABLE_NAME IS NOT NULL
      ORDER BY TABLE_NAME, CONSTRAINT_NAME
    `);

        console.log(`\n📋 当前共有 ${constraints.length} 个外键约束:`);
        constraints.forEach(constraint => {
            console.log(`   ${constraint.TABLE_NAME}.${constraint.COLUMN_NAME} -> ${constraint.REFERENCED_TABLE_NAME}.${constraint.REFERENCED_COLUMN_NAME}`);
        });

        // 生成外键约束报告
        const report = {
            timestamp: new Date().toISOString(),
            summary: {
                totalStatements: statements.length,
                successCount: successCount,
                errorCount: errorCount,
                foreignKeyConstraints: constraints.length
            },
            constraints: constraints
        };

        fs.writeFileSync('./foreign-keys-report.json', JSON.stringify(report, null, 2));
        console.log('\n📄 外键约束报告已保存到 foreign-keys-report.json');

    } catch (error) {
        console.error('❌ 执行过程中发生错误:', error.message);
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

executeAllForeignKeys();
